import pandas as pd
import numpy as np
import os
from WindPy import w
w.start()

data=pd.read_excel('314-5_20200103.xls')
position=list(data['持仓'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
mv=list(data['市值'])
price=list(data['最新价'])
codeList=[]
for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
        
    code=int(stocks[i])
    pos=position[i]
    pos=np.floor(pos/400)*100
    codeStr=str(code).rjust(6,'0')
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
    else:
        continue
    codeList.append({'code':codeStr,'hold':pos,'name':names[i],'marketValue':mv[i],'price':price[i]})
myStocks=pd.DataFrame(codeList)
codes=list(myStocks['code'])
with pd.HDFStore('314005total.h5','r',complib='blosc:zstd',append=False,complevel=9) as store:
    total=store['data']
total=total[total['date']>='20191001']
p1=0.0035
p2=0.8
myselect0=total[(abs(total['parameter1']-p1)<0.0001)& (abs(total['parameter2']-p2)<0.0001)]
myselect0['holdAmount']=myselect0['dailyOpen']*myselect0['originalPosition']
myanalysistotal=myselect0.groupby(['code'])['profit'].sum()
myanalysistotal=pd.DataFrame(myanalysistotal)
myanalysistotal['yieldToAll']=myselect0.groupby(['code'])['yieldToAll'].sum()
myanalysistotal['winRate']=myselect0.groupby(['code'])['winRate'].mean()
myanalysistotal['hold']=myselect0.groupby(['code'])['hold'].mean()
myanalysistotal['amount']=myselect0.groupby(['code'])['amount'].mean()
myanalysistotal['count']=myselect0.groupby(['code'])['count'].mean()
myanalysistotal['parameter1']=myselect0.groupby(['code'])['parameter1'].mean()
myanalysistotal['parameter2']=myselect0.groupby(['code'])['parameter2'].mean()
myanalysistotal['holdAmount']=myselect0.groupby(['code'])['holdAmount'].mean()
myanalysistotal.sort_values(by=['yieldToAll'])
choose=list(myanalysistotal[myanalysistotal['yieldToAll']>myanalysistotal['yieldToAll'].mean()].index)
stock=myStocks[myStocks['code'].isin(choose)]
stock['selectmv']=stock['price']*stock['hold']
select=stock[stock['hold']>=300]
select=select[['code','hold']]
select['parameter1']=0.0035
select['parameter2']=0.8
select.to_csv("stocks314005.csv",index=0,header=0)
control=select[['code','hold']]
control['hold']=control['hold'].astype('int32')
control.to_csv("control314005.csv",index=0,header=0)
stocks=myStocks[myStocks['hold']>=300]
stocks['marketValue']=stocks['hold']*stocks['price']
stocks=stocks[stocks['code'].isin(choose)]
stocks['marketValue'].sum()




